package parsing;

import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Locale;
import java.util.Map;
import java.util.Scanner;
import java.util.Set;

import jxl.*;
import jxl.read.biff.BiffException;
import parsing.Element;

import java.util.Random;


public class Pars_XLS {

	public static ArrayList<Element> parsing(File path) 
			throws IOException, BiffException{
		Element element;
		Element parent;
		ArrayList<Element> elList = new ArrayList<Element>() ;
		
		Workbook workbook = Workbook.getWorkbook(path);
		Sheet sheet = workbook.getSheet(0); 
		int nameColumn = -1, typeColumn = -1, portColumn = -1, portStatusColumn = -1, cardColumn = -1, locationColumn = -1, dateColumn = -1;
		for (int i=0;i<sheet.getColumns();i++) {
			switch (sheet.getCell(i, 0).getContents().toLowerCase(Locale.ENGLISH)) {
			case "name":
				nameColumn = i;
				break;
			case "type":
				typeColumn = i;
				break;
			case "port":
				portColumn = i;
				break;
			case "port_status":
				portStatusColumn = i;
				break;
			case "card":
				cardColumn = i;
				break;
			case "location":
				locationColumn = i;
				break;
			case "date":
			    dateColumn = i;
				break;
				
			}
			
		}
		
		

		for (int i=1;i<sheet.getRows();i++) {
		   element = new Element(sheet.getCell(nameColumn, i).getContents());
		   String location = sheet.getCell(locationColumn, i).getContents();
		      
		   String[] locList = location.split("/");
		        parent = parsChaild(elList, "country", null, locList[0]);
		        parent = parsChaild(elList, "city",parent, locList[1]);
		        parent = parsChaild(elList, "district",parent, locList[2]);
		        parent = parsChaild(elList, "street", parent, locList[3]);
		        parent = parsChaild(elList, "house",parent, locList[4]);
		        parent = parsChaild(elList, "porch",parent, locList[5]);
		        element.setParent(parent);
		   	    element.setType(sheet.getCell(typeColumn, i).getContents().toLowerCase(Locale.ENGLISH));
		        
		   	    element.setAttribute("date", sheet.getCell(dateColumn, i).getContents().toLowerCase(Locale.ENGLISH));
		   	    elList.add(element);  
			   
		     
		  if (!(sheet.getCell(cardColumn, i).getContents().toLowerCase(Locale.ENGLISH)== "")){  
			  parent = parsChaild(elList, cardColumn, element, i, sheet, -1);
		    parsChaild(elList, portColumn, parent, i, sheet, portStatusColumn);
		  } else {
			parsChaild(elList, portColumn, element, i, sheet, portStatusColumn);
		  }
		}
	 return elList;	
	}	

	
    public static Element parsChaild(ArrayList<Element> elList, 
    		                   int nowType, Element parent, 
    		                   int nowRow, Sheet sheet, int status) {
    	
		Element element = new Element(sheet.getCell(nowType, nowRow).getContents());
		element.setParent(parent);
		element.setType(sheet.getCell(nowType, 0).getContents().toLowerCase(Locale.ENGLISH));
		
		if (!(status == -1)) {
		   element.setAttribute("status", sheet.getCell(status, nowRow).getContents());
		}
		
    	elList.add(element);
    	return element;
    }

    public static Element parsChaild(ArrayList<Element> elList, 
    		String Type, Element parent, String name) {
    	
            Element element = new Element(name);
            element.setParent(parent);
            element.setType(Type);

            elList.add(element);
            
            return element;
 }
}
